package database.dbObjects;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import main.Main;
import objects.Contact;

public interface IDBContact extends IDBObject {
	
	public class DBContact extends DBObject {
		
		/*
		 * methods are final for optimization of the code (by the compiler)
		 * it is not possible anymore to inherit it after this declaration 
		 */
		
		public static final int minLengthEmail = 5;
		public static final int maxLengthEmail = 50;
		
		public void init() throws Exception {
			
			createTable();
			createTriggers();
		}
		
		private void createTable() throws Exception {
			
			String query = "create table Contact( " +
			   			   "Email varchar(" + maxLengthEmail + ") unique not null, " +
						   "Name varchar(" + maxLengthName + ") not null, " +
						   "Forename varchar(" + maxLengthName + ") not null, " +
						   "PhoneNumber varchar(" + maxLengthPhoneNr + "), " +
						   "MoreInformation varchar(" + maxLengthInfo + "), " +
						   "primary key (Email) " +
						       "on conflict abort);";
	        
			executeCreation(query);
		}
		
		private final void createTriggers() throws Exception {
			
			String query;
			query = "create trigger update_contact_email after update of Email on Contact " +
			   	    "begin " +
			   	    	"update Student set Email = new.Email " +
			   	    		"where (Email = old.Email); " +
				    	"update Teacher set Email = new.Email " +
				    		"where (Email = old.Email); " +
				    "end;";
			executeCreation(query);
			
			query = "create trigger delete_contact_email before delete on Contact " +
					"begin " +
	    	   			"delete from Student where (Email = old.Email); " +
	    	   			"delete from Teacher where (Email = old.Email); " +
	    	   		"end;";
			executeCreation(query);
		}
		
		public final boolean add(String email, String name, String fname,  
				String phoneNr, String moreInfo) throws Exception {
			
			checkConsistance(email, name, fname, phoneNr, moreInfo);
			
			Vector<Object> v = new Vector<Object>(5);
			v.add(0, email);
			v.add(1, name);
			v.add(2, fname);
			v.add(3, phoneNr);
			v.add(4, moreInfo);
			
			return super.add("Contact", v);
		}
		
		public final boolean delete(String email) throws Exception {
			
			checkEmail(email);
			
			return super.delete("Contact", "Email", email);
		}
		
		public final boolean updateEmail(String email, String newEmail) throws Exception {
			
			checkEmail(email);
			checkEmail(newEmail);
			
			return super.update("Contact", "Email", email, "Email", newEmail);
		}
		
		public final boolean updateName(String email, String newName) throws Exception {
			
			checkEmail(email);
			checkName(newName);
			
			return super.update("Contact", "Email", email, "Name", newName);
		}
		
		public final boolean updateForename(String email, String newFname) throws Exception {
			
			checkEmail(email);
			checkName(newFname);
			
			return super.update("Contact", "Email", email, "Forename", newFname);
		}
		
		public final boolean updatePhoneNumber(String email, String newPhoneNr) throws Exception {
			
			checkEmail(email);
			checkPhoneNumber(newPhoneNr);
			
			return super.update("Contact", "Email", email, "PhoneNumber", newPhoneNr);
		}
		
		public final boolean updateMoreInformation(String email, String newInfo) throws Exception {
			
			checkEmail(email);
			checkMoreInformation(newInfo);
			
			return super.update("Contact", "Email", email, "MoreInformation", newInfo);
		}
		
		public Contact getContact(String email) throws Exception {
			// Returns the requested object or null if nothing was found
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Contact " +
											 "where (Email = '" + email + "');");
			if (rs.next()) {
				Contact c = new Contact(rs.getString("Email"), rs.getString("Name"),
						rs.getString("Forename"), rs.getString("PhoneNumber"), 
						rs.getString("MoreInformation"));
				rs.close();
				return c;
			}
			rs.close();
			return null;
		}
		
		public Vector<Contact> getAllContacts() throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Contact " + 
					"where (Email not in (select Email from Student) and " + 
					"Email not in (select Email from Teacher));");
			
			Vector<Contact> v = new Vector<Contact>();
			int counter = 0;
			while (rs.next()) {
				v.add(counter, new Contact(rs.getString("Email"), rs.getString("Name"),
					rs.getString("Forename"), rs.getString("PhoneNumber"), 
					rs.getString("MoreInformation")));
				counter++;
			}
			
			rs.close();
			return v;
		}
		
		protected final void checkConsistance(String email, String name, String fname, 
				String phoneNr, String moreInfo) throws Exception {
			
			checkEmail(email);
			checkName(name);
			checkName(fname);
			checkPhoneNumber(phoneNr);
			checkMoreInformation(moreInfo);
		}

		public final void checkEmail(String email) throws Exception {
			
			if (email == null) { throw (new Exception("Email needed!")); }
			if (email.length() < minLengthEmail) {
				throw (new Exception("Email has to be at least " + minLengthEmail 
						+ " characters long!"));
			}
			if (email.length() > maxLengthEmail) {
				throw (new Exception("Email exceeds maximum length (" +
						maxLengthEmail + " characters)!"));
			}
// TODO checken! (wss ok)
			String pattern = "(\\S+)(\\@)((\\w+\\.)+)(\\w+)";
            if (!email.matches(pattern)) {
                throw (new Exception("Email syntax error!"));
            }
		}
		
		protected final void checkName(String name) throws Exception {
			
			if (name == null) { throw (new Exception("Name needed!")); }
			if (name.length() < 1) {
				throw (new Exception("Name has to be at least 1 character long!"));
			}
			if (name.length() > maxLengthName) {
				throw (new Exception("Name exceeds maximum length (" +
						maxLengthName + " characters)!"));
			}
		}
		
		protected final void checkPhoneNumber(String phoneNr) throws Exception {
			
			if (phoneNr == null) { phoneNr = ""; }
			if (phoneNr.length() < 9) {
				throw (new Exception("PhoneNumber has to be at least " + minLengthPhoneNr + 
					" characters long!"));
			}
			if (phoneNr.length() > maxLengthPhoneNr) {
				throw (new Exception("PhoneNumber exceeds maximum length (" +
						maxLengthPhoneNr + " characters)!"));
			}
		}
		
		protected final void checkMoreInformation(String moreInfo) throws Exception {
			
/** TODO set moreInfo on default value */
			
			if (moreInfo == null) { moreInfo = "Nothing."; }
			if (moreInfo.length() > maxLengthInfo) {
				throw (new Exception("MoreInformation exceeds maximum length (" +
						maxLengthInfo + " characters)!"));
			}
		}
		
		
		private final int maxLengthName = 50;
		private final int minLengthPhoneNr = 9;
		private final int maxLengthPhoneNr = 20;
		private final int maxLengthInfo = 200;
		
	}
	
}
